package com;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement; 
import java.util.ArrayList;
import java.util.List;
public class SqliteExec {

	private Connection connection=null;
	private Statement statement =null;
	//private SQLiteDatabase  db=null;
	private static SqliteExec mSqliteExec =getInstance();
	

	public SqliteExec() {
		try{
		 this.connection = DriverManager.getConnection("jdbc:sqlite:test.db");
		 this.statement = connection.createStatement();  
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
	}
	
	public void createTable(String sql){
		
		 insert(sql);
		
	}
	public  boolean executeSql(String sql){
		
		try{
		return statement.execute(sql);
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		return false;
	}
	public boolean  isTableExist(String tableName){
		
		boolean isTableExist=false;
		String sql = "select count(*) from sqlite_master where type='table' and name='"+tableName+"'";
		Logs.i(sql);
		try{
		ResultSet rs = this.statement.executeQuery(sql);
		Logs.i(rs.getInt(1)+"");
		if(rs.getInt(1) == 0){
			
			isTableExist = false;
		}else{
			isTableExist = true;
		}
		rs.close();
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		return isTableExist;
	}
	static public <T> List<T> findAll(Class<T> modelClass,String tableName ) {
		List<T> dataList = new ArrayList<T>();
		Logs.i(tableName);
		String sql="select * from  "+tableName ;
		ResultSet rs ;
		Statement s  ;
		try{
			s  = mSqliteExec.connection.createStatement();
			// if the same statemnt use to exec diff sql it will auto closed  when second use ;
			rs = s.executeQuery(sql); 
			Field[] fs = modelClass.getDeclaredFields();
			int n = fs.length;
			if(rs != null){
				while(rs.next())  
				{  
	    	  
	    	
	    	 
					
					T modelInstance = (T) modelClass.newInstance();
				//Logs.i(rs.getString("name"));
					for(int i=0; i< n ; i++){
						fs[i].setAccessible(true);
						fs[i].set(modelInstance,rs.getString(fs[i].getName()));
	    	    
	    	    
					}
	    	
	    	  dataList.add(modelInstance);
				}  
	      
	      
			}
			

			
			rs.close();
			s.close();
		
   	 }catch(Exception e){
   		 
   		e.printStackTrace();
   	 }
		return  dataList;
		
	}
	
	
	static public SqliteExec getInstance(){
		
		if(mSqliteExec == null){
			
			mSqliteExec = new SqliteExec();
		}
		
		return mSqliteExec;
	}
	public  boolean  insert(String sql){
		try{
			if(this.statement != null){
				int  line=statement.executeUpdate(sql);
				if(line >0){
					
					return true;
				}
		
			}
	
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		return false;
	}
	
	public  static void main(String[] argcs){
	
		
		try{
			Class.forName("org.sqlite.JDBC");
			Connection  connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			 Statement statement = connection.createStatement();  
		      statement.setQueryTimeout(30);  // set timeout to 30 sec.  
		  
		      statement.executeUpdate("drop table if exists person");  
		      statement.executeUpdate("create table person (id integer, name string)");  
		      statement.executeUpdate("insert into person values(1, 'leo')");  
		      statement.executeUpdate("insert into person values(2, 'yui')");  
		      ResultSet rs = statement.executeQuery("select * from person");  
		      while(rs.next())  
		      {  
		        // read the result set  
		        System.out.println("name = " + rs.getString("name"));  
		        System.out.println("id = " + rs.getInt("id"));  
		      }  
		}catch(Exception e){
		
		e.printStackTrace();
	}
	
	
	
	}
	 
	 
}
